SQL Server transactions can produce results that seem wrong even when the code looks correct. Perhaps it’s a row count that doesn’t match, a query that waits when it shouldn’t, or an audit record that vanishes after a rollback. This article works through five such problems as hands-on tasks, each with a setup script, an explanation of why it happens, and a solution.

The problems (and their solutions) covered in this guide:

A query blocks while a long-running transaction is still inserting rows

A transaction reads different values for the same row twice

A transaction gets a different row count without inserting or deleting anything itself

A rolled-back transaction wipes out the audit record with it

A nested rollback undoes more than it should

I encourage you to try identifying and solving the problem yourself, before reading my solution. Also, please note: each task should be tested independently and, before starting a new task, run the setup script associated with it.

The problems (and their solutions)

Without further ado, let’s move onto the problems – and, most importantly, the solutions I recommend for each.

The problem: a query blocks while a long-running transaction is still inserting rows

Suppose we have a table named TestTable with the following structure:

We are inserting a large amount of data into TestTable using the query below:

As the transaction runs for a long time, we want to periodically check how many rows have been inserted into the table so far.

So, we open another query window, and run the SELECT COUNT(*) FROM TestTable query.

This query waits until the first query finishes and then returns the result:

How can we rewrite the query to check the number of rows inserted so far without waiting for the transaction to complete? Let’s find out.

The solution: read uncommitted data with NOLOCK or READ UNCOMMITTED (how-to guide)

The reason the second query waits for the first transaction to complete before returning the result is that it uses the READ COMMITTED transaction isolation level by default (this example assumes the database is using the default locking behavior for the READ COMMITTED isolation level).

This isolation level prevents dirty reads.

In simple terms, showing the row count while rows are still being inserted into the table may not be accurate because the transaction has yet to be completed (so can still be rolled back.)

However, since we want to check the number of inserted rows at a specific moment, we can allow dirty reads – bearing in mind that the result may include rows that can be rolled back later on.

To do this, in the second query, we can use the WITH (NOLOCK) table hint (Solution 1), or set the transaction isolation level for the session to READ UNCOMMITTED (Solution 2):

It’s worth mentioning that NOLOCK can also produce inaccurate results due to concurrent data modifications, such as missing or reading the same rows multiple times.

The problem: a transaction reads different values for the same row twice

Suppose we have TestTable with the following structure:

We are running the following transaction, which has many other statements between “Statement 1” and “Statement 2”.

In this example, we are using WAITFOR DELAY instead of these statements to simplify the demonstration:

After “Statement 1” finishes and before “Statement 2” starts, “Query 2” is executed in another query window:

When “Query 1” finishes, we can see that the result of the last SELECT statement is confusing as there’s no person with the full name returned by the query:

However, the final SELECT statement needs to return the full name of an existing person.

How can we modify “Query 1” to make it work correctly without changing “Statement 1” and “Statement 2”, which must assign the first and last names of the same person to the variables, respectively?

Let’s find out.

The solution: use REPEATABLE READ to prevent non-repeatable reads (how-to)

The issue happens because the default READ COMMITTED isolation level protects each individual statement from reading uncommitted data.

It does not, however, guarantee that two separate SELECT statements inside the same transaction will read the same version of the data.

In this example, after “Statement 1” reads the first name for user2@example.com, another query can update the email values before “Statement 2” reads the last name. This results in both “Statement 1” and “Statement 2” being able to read data from different rows.

To solve this problem, we can use the REPEATABLE READ isolation level. It keeps shared locks on the rows read by the transaction until the transaction is actually complete, so another query can’t update the rows between “Statement 1” and “Statement 2”.

As a result, the first name and last name will be read from the same person.

Before testing the solution, recreate TestTable using the original setup script:

The problem: a transaction gets a different row count without inserting or deleting anything itself

Let’s consider a table named TestTable with the following structure:

We are running the following transaction, which has many other statements between “Statement 1” and “Statement 2”. We use WAITFOR DELAY instead of these statements to simplify the demonstration:

After “Statement 1” finishes and before “Statement 2” starts, “Query 2” is executed in another query window:

When “Query 1” finishes, we can see that different row counts are returned for TestTable at the beginning and at the end of our transaction (although we didn’t add or remove any rows in the current transaction):

However, we need to get the same row count for TestTable even when we don’t add or delete rows in the current transaction.

How, then, can we modify “Query 1” to make it work correctly? After all, “Statement 1” and “Statement 2” must return the same result if the row count hasn’t changed inside the current transaction.

Here’s how to ensure that happens.

The solution: use SERIALIZABLE to prevent phantom reads (how-to)

The issue happens because, under the default READ COMMITTED isolation level, “Statement 1” and “Statement 2” are executed separately and another session can insert new rows between them.

As a result, the row count can be different at the beginning and at the end of the same transaction.

To avoid this, we can use the SERIALIZABLE isolation level. This prevents SQL Server from allowing another transaction to insert new rows into the range read by the current transaction until it’s complete – meaning “Statement 1” and “Statement 2” will return the same row count.

Before testing the solution, recreate TestTable using the original setup script:

The problem: a rolled-back transaction wipes out the audit record with it

We need to audit user logins in the system. We have a LoginAudit table to store information about user logins:

User authentication occurs within a transaction, which may be rolled back:

When the transaction is rolled back, we can see that there are no audit records for this login:

So, how can we modify the code above to record user login information even when the transaction is rolled back? Let’s find out.

The solution: use a table variable to survive a rollback (and how to do it)

We can use a table variable instead of a temporary table inside the transaction to store audit information. This is because ROLLBACK TRANSACTION does not erase changes made to table variables.

After the transaction is rolled back, we can still insert the audit information from the table variable into the LoginAudit table:

We can now see the result in the LoginAudit table:

Please note that this example is simplified for demonstration purposes. In real systems, the password verification logic, authentication and audit logging should be designed carefully based on security and compliance requirements.

The problem: a nested rollback undoes more than it should

In the previous task, we used a table variable to keep audit information after rollback. Now, let’s consider another case where we work with permanent tables inside the transaction and need to roll back only part of the work.

Suppose we have both the LoginAudit and UserLogin tables:

We need to insert login audit information into LoginAudit first, and then insert login information into UserLogin.

If something goes wrong during the insert into UserLogin, we need to roll back that insert only, but still also need to keep the audit record in LoginAudit.

A developer wrote the following code using a nested transaction, expecting that the ROLLBACK within it would only roll back the insert into UserLogin:

However, when ROLLBACK TRANSACTION is executed, the whole transaction is rolled back – and the audit record isn’t saved either:

So, how can we modify the code to only roll back the insert into UserLogin and, at the same time, keep the audit record in LoginAudit? Let’s find out.

The solution: use SAVE TRANSACTION to roll back to a savepoint (and how to do so)

The issue happens because ROLLBACK TRANSACTION inside a nested transaction does not just roll back the inner transaction – in SQL Server, it rolls back the outer transaction as well.

The only exception is if a savepoint is specified in the ROLLBACK statement. And, since in our case both the insert into LoginAudit and the insert into UserLogin are rolled back, we can use the savepoint as our solution.

After inserting the audit information into LoginAudit, we can create a savepoint by using SAVE TRANSACTION.

Then, if something goes wrong while inserting into UserLogin, we can roll back to that savepoint only, and commit the remaining part of the transaction:

As a result, the insert into UserLogin will be rolled back, but the audit record in LoginAudit will be saved:

It’s worth noting that in real-world scenarios, some errors can leave a transaction in an uncommittable state, and rolling back to a savepoint becomes impossible.

Also be aware that, for simplicity, this example only demonstrates a failed operation.

Conclusion

Working with transactions can be tricky, and writing transaction-heavy SQL code requires a fundamental understanding of transaction management nuances, isolation levels, rollback mechanisms, and more.

In this article, I’ve shed some light on a handful of common mistakes in transaction management (and offered potential solutions to them) – but there are many others. In general, a deep understanding of the theory is strongly recommended.

Useful resources to help develop your understanding of transactions